---
title: 使用PXF(JDBC)访问SQL数据库
---

<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

  http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
-->

您的某些数据可能已经存储在外部SQL数据库中。 PXF通过PXF JDBC连接器提供对此数据的访问。 JDBC 连接器是一个JDBC客户端。 它可以从SQL数据库(包括MySQL, ORACLE, PostgreSQL, Apache Ignite和Hive)读取或向SQL数据库写入数据。

本节描述如何使用PXF JDBC 连接器访问外部SQL数据库中的数据，包括如何创建引用外部数据库表的PXF外部表，向该表查询数据或将数据插入该表中。

<div class="note">写入外部SQL数据库时，JDBC 连接器不保证一致性。 请注意，如果 <code>INSERT</code> 操作失败，部分数据可能会写入外部数据库表中。 如果您需要写操作的一致性，请考虑写入到外部数据库的临时表，并仅在验证写操作后才加载到目标表。</div>

## <a id="prereq"></a>先决条件

在您使用PXF JDBC连接器访问外部数据库前，请确保：

- 您已在Greenplum数据库segment主机上配置并初始化PXF, 并且PXF正在每台segment主机上运行。 更多详情，请参阅[配置 PXF](instcfg_pxf.html)。
- 您可以确定PXF 用户配置目录(`$PXF_CONF`)。
- 所有Greenplum数据库segment主机和外部SQL数据库库之间都可以连接。
- 您已经配置了外部SQL数据库，以便从所有Greenplum数据库segment主机进行访问。
- 您已经注册了所有JDBC驱动程序的JAR依赖。
- (推荐)您已经按照 [配置PXF JDBC 连接器](jdbc_cfg.html) 中的描述创建了一个或多个命名的PXF JDBC连接服务配置。

## <a id="datatypes"></a>数据类型支持

PXF JDBC 连接器支持以下数据类型:

- INTEGER, BIGINT, SMALLINT
- REAL, FLOAT8
- NUMERIC
- BOOLEAN
- VARCHAR, BPCHAR, TEXT
- DATE
- TIMESTAMP
- BYTEA

PXF JDBC 连接器不支持上面未列出的任何数据类型。

## <a id="queryextdata"></a>访问外部SQL数据
PXF JDBC连接器支持一个名为 `Jdbc` 的配置文件。 您可以使用此配置文件从外部SQL数据库表读取数据或将数据写入外部SQL数据库表。

要访问外部SQL数据库中的数据，请创建一个可读或可写的Greenplum数据库外部表，该表引用外部数据库表。 Greenplum和外部数据库表必须具有相同的定义： 列名，类型和顺序必须匹配。

使用以下语法创建引用外部SQL数据库表的Greenplum数据库外部表，并使用JDBC连接器读取或写入数据：

<pre>
CREATE [READABLE | WRITABLE] EXTERNAL TABLE &lt;table_name>
    ( &lt;column_name> &lt;data_type> [, ...] | LIKE &lt;other_table> )
LOCATION ('pxf://&lt;external-table-name>?<b>PROFILE=Jdbc[&SERVER=&lt;servername>]</b>[&&lt;custom-option>=&lt;value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');
</pre>

[CREATE EXTERNAL TABLE](../ref_guide/sql_commands/CREATE_EXTERNAL_TABLE.html)命令中使用的特定关键字和值见下表中描述。

| 关键字  | 值 |
|-------|-------------------------------------|
| \<external&#8209;table&#8209;name\>    | 外部表的全名。 取决于外部SQL数据库，可能包括模式名称和表名称。 |
| PROFILE    | `PROFILE` 关键字必须指定为 `Jdbc`. |
| SERVER=\<servername\>   |  PXF 访问外部SQL数据库时使用的服务配置 |
| \<custom&#8209;option\>  | \<custom-option\> 是特定于配置文件的。 `Jdbc` 配置文件的选项将在下一部分讨论。|
| FORMAT 'CUSTOM' | JDBC `CUSTOM` `FORMAT` 支持用于读取操作的内置 `'pxfwritable_import'` `FORMATTER` 函数和用于写入操作的内置 `'pxfwritable_export'` 函数 |

**注意**: 在创建PXF外部表时，不能在 `FORMAT` 规范中使用 `HEADER` 选项。


### <a id="jdbcoptions"></a>JDBC自定义选项

您在`LOCATION` URI中引入JDBC自定义选项，并在每个选项前加上 `&` 符。

`Jdbc` 配置文件支持以下连接相关的\<custom-option\>：

| 自定义选项名称   | jdbc-site.xml 属性名称 | 描述
|----------------------|-----------------------------|--------|
| JDBC_DRIVER | jdbc.driver | JDBC驱动类名 (必须) |
| DB_URL | jdbc.url | 外部数据库URL。 取决于外部数据库，通常至少包括主机名，端口和数据库名称 (必须) |
| USER | jdbc.user | 数据库用户名。 如果提供了 `PASS` 则为必须。 |
| PASS | jdbc.password |  `USER` 的数据库密码。.如果提供了 `USER` 则为必须。 |

<div class="note">如果在<code>CREATE EXTERNAL TABLE</code> <code>LOCATION</code> 子句中提供了 <code>SERVER=&lt;servername></code>，则<code>LOCATION</code> 子句中包含的任何连接选项都会覆盖<code>&lt;servername></code>的<code>jdbc-site.xml</code>配置文件中指定的值。</div>

示例 JDBC \<custom-option\> 连接字符串：

``` pre
&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://gpmaster:5432/pgtestdb&USER=pguser1&PASS=changeme
&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://mysqlhost:3306/testdb&USER=user1&PASS=changeme
```

`Jdbc` 配置文件支持的其他 `CREATE EXTERNAL TABLE` \<custom-option\> 包括：

| 选项名称   | 操作 | 描述
|---------------|------------|--------|
| BATCH_SIZE | Write | 整数，标识要批处理到外部SQL数据库的 `INSERT` 操作数量。 PXF 始终会验证 `BATCH_SIZE` 选项，即使是在读取操作中提供。 默认开启批处理。 |
| POOL_SIZE | Write | 在`INSERT`操作上启动线程池，并标识线程池中的线程数。默认情况下，线程池是禁用的。 |
| PARTITION_BY | Read | 分区列 \<column-name\>:\<column-type\>。 您只能指定一个分区列。JDBC 连接器支持 `date`、 `int` 和 `enum` \<column-type\> 值。 空的 `PARTITION_BY` 默认为单个片段。 |
| RANGE | Read | 当指定 `PARTITION_BY` 时为必须。 查询范围： \<start-value\>[:\<end-value\>]。 当分区列为 `enum` 类型时， `RANGE` 必须指定一个值列表，每个值形成自己的分区。如果分区列是 `int` 或 `date` 类型，`RANGE` 必须指定一个有限的左闭区间。 即， 范围包括 \<start-value\> 但 *不* 包括 \<end-value\>。 如果分区列是 `date` 类型， 使用 `yyyy-MM-dd` 日期格式。 |
| INTERVAL | Read | 指定 `PARTITION_BY` 且为 `int` 或 `date` 类型时必须。。 一个分区的间隔：\<interval-value\>[:\<interval-unit\>]。 在 \<interval-value\> 中指定分区的大小。 如果分区列是 `date` 类型, 请使用 \<interval-unit\> 指定 `year`, `month` 或 `day`。 |
| QUOTE_COLUMNS | Read | 控制在构造外部数据库的SQL查询是 PXF 是否应引用列名。 指定为 `true` 强制PXF引用所有列名称; 如果指定任何其他值，PXF不会引用列名。 如果未指定 `QUOTE_COLUMNS` (默认)， 当查询中 *任一* 字段满足以下条件， PXF自动引用 *所有* 列名： <br>- 包含特殊字符, 或 <br>- 混合大小写，并且外部数据库不支持未引用的混合大小写标识符。 |


#### <a id="batching"></a>批量写入操作(写)

当外部SQL数据库的JDBC 驱动程序支持它是，批量 `INSERT` 操作可能会大大提升性能。

默认情况下启用批处理， 默认批处理大小为100。 要禁用批处理或修改批处理大小的值，请使用 `BATCH_SIZE` 设置创建PXF外部表：

- `BATCH_SIZE=0` 或 `BATCH_SIZE=1` - 关闭批处理
- `BATCH_SIZE=(n>1)` - 将 `BATCH_SIZE` 设置为 `n`

当外部数据库的JDBC驱动程序不支持批处理时，PXF JDBC 连接器的行为取决于 `BATCH_SIZE` 设置，如下所述：

- `BATCH_SIZE` 省略 - JDBC 连接器插入时不使用批处理。
- `BATCH_SIZE=(n>1)` - `INSERT` 操作失败并且连接器返回错误。

#### <a id="threadpool"></a>线程池(写)

当外部数据库的JDBC驱动程序支持线程化时，PXF JDBC连接器可以通过在多个线程中处理 `INSERT` 操作来进一步提升性能。

考虑将批处理和线程池一起使用。 当一起使用时，每个线程将接收并处理一批完整的数据。如果您使用线程池而不使用批处理，则线程池中的每个线程都恰好接收一个元组。

当线程池中的任一线程失败时，JDBC 连接器返回一个错误。请注意 `INSERT` 操作失败，部分数据可能会写入外部数据库表中。

要禁用或启动线程池并设置线程池大小，请使用 `POOL_SIZE` 设置创建PXF外部表，如下所述：

- `POOL_SIZE=(n<1)` - 线程池大小是系统中的CPU数量
- `POOL_SIZE=1` - 关闭线程池
- `POOL_SIZE=(n>1)`- 将 `POOL_SIZE` 设为 `n`

#### <a id="partitioning"></a>分区(读)

PXF JDBC 连接器支持运行在多个segment主机上的PXF实例同时对外部SQL表的读取访问。 此功能被称为分区

当您指定 `PARTITION_BY` 选项时，请根据与目标数据库的最佳连接数以及跨Greenplum数据库segment的最佳片段分布来调整 `INTERVAL` 值和单位。 `INTERVAL` 低边界由Greenplum数据库segment的数据量驱动，而高边界由目标数据库可接受的JDNC连接数驱动。 `INTERVAL` 设置会影响片段的数量， 并且理想情况下不应设置的太高或太低。 使用多个值进行测试可以帮助您选择最佳配置。

标识分区参数的示例JDBC \<custom-option\> 子字符串：

``` pre
&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1
&PARTITION_BY=createdate:date&RANGE=2013-01-01:2016-01-01&INTERVAL=1:month
&PARTITION_BY=color:enum&RANGE=red:yellow:blue
```

启用分区后，PXF JDBC连接器会将 `SELECT` 查询拆分为一组小查询，每个小查询都称为一个片段。所有PXF实例同时处理一个片段。

JDBC 连接器会自动向每个片段添加额外的查询约束 (`WHERE` 表达式)，以确保从外部数据库中检索每个数据元组都只需一次。


### <a id="jdbc_example_postgresql"></a>示例: 读取和写入PostgreSQL表

在本例中，您将：

- 创建一个PostgreSQL数据库和表，并将数据写入表中
- 创建一个PostgreSQL用户并将表上的所有权限都赋予该用户
- 配置PXF JDBC 连接器以访问PostgreSQL数据库
- 创建一个引用PostgreSQL表的PXF可读外部表
- 读取PostgreSQL表中的数据
- 创建一个引用PostgreSQL表的PXF可写外部表
- 将数据写入PostgreSQL表
- 再次读取PostgreSQL表中的数据

#### <a id="ex_create_pgtbl"></a>创建一个PostgreSQL表

执行以下步骤在名为 `pgtestdb` 的数据库的 `public` 模式下创建一个名为 `forpxf_table1` 的PostgreSQL表，并向名为 `pxfuser1` 的用户赋予该表的所有权限：

1. 确定PostgreSQL服务器的主机名和端口。

2. 以 `postgres` 用户连接默认PostgreSQL数据库。 例如，假设您的PostgreSQL服务以默认端口运行在 `pserver` 主机上：

    ``` shell
    $ psql -U postgres -h pserver
    ```

3. 创建一个名为`pgtestdb`的PostgreSQL数据库并连接到这个数据库：

    ``` sql
    =# CREATE DATABASE pgtestdb;
    =# \connect pgtestdb;
    ```

4. 创建一个名为 `forpxf_table1` 的表并并向表中写入一些数据：

    ``` sql
    =# CREATE TABLE forpxf_table1(id int);
    =# INSERT INTO forpxf_table1 VALUES (1);
    =# INSERT INTO forpxf_table1 VALUES (2);
    =# INSERT INTO forpxf_table1 VALUES (3);
    ```

5. 创建一个名为 `pxfuser1` 的PostgreSQL用户：

    ``` sql
    =# CREATE USER pxfuser1 WITH PASSWORD 'changeme';
    ```
 
6. 为用户 `pxfuser1` 分配 `forpxf_table1` 表的所有权限，并退出 `psql` 子系统：

    ``` sql
    =# GRANT ALL ON forpxf_table1 TO pxfuser1;
    =# \q
    ```

    有了这些权限， `pxfuser1` 可以读取和写入 `forpxf_table1` 表。

7. 更新 PostgreSQL 配置以允许用户 `pxfuser1` 从每个Greenplum数据库segment主机访问 `pgtestdb` 。 此配置特定于您的PostgreSQL环境。 您将更新 `/var/lib/pgsql/pg_hba.conf` 文件，然后重启PostgreSQL服务。


#### <a id="ex_jdbconfig"></a>配置JDBC连接器

您必须为PostgreSQL创建JDBC服务配置，将PostgreSQL驱动程序JAR文件下载到您的系统，将该JAR文件复制到PXF用户配置目录，同步PXF配置， 然后重启PXF。

1. 登录到Greenplum数据库master节点：

    ``` shell
    $ ssh gpadmin@<gpmaster>
    ```

2. 如[示例配置步骤](jdbc_cfg.html#cfg_proc)中所述为PostgreSQL创建JDBC服务配置，命名服务目录为 `pgsrvcfg`。 `jdbc-site.xml` 文件的内容应类似于以下内容(将PostgreSQL主机系统替换为 `pgserverhost`)：

    ``` xml
    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>org.postgresql.Driver</value>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:postgresql://pgserverhost:5432/pgtestdb</value>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>pxfuser1</value>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>changeme</value>
    </property>
</configuration>
    ```

2. [下载](https://jdbc.postgresql.org/download.html) PostgreSQL JDBC驱动程序JAR文件，并记下文件的下载位置。

3. 将JDBC驱动程序JAR文件复制到Greenplum数据库master主机上的 `$PXF_CONF/lib` 。 例如：

    ``` shell
    gpadmin@gpmaster$ cp postgresql-42.2.5.jar $PXF_CONF/lib/postgresql-42.2.5.jar
    ``` 

3. 同步PXF 配置到所有Greenplum数据库segment主机。例如：

    ``` shell
    gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster sync
    ``` 

6. 如[重启PXF](cfginitstart_pxf.html#restart_pxf)中所述，在每个Greenplum数据库segment主机上重启PXF。

#### <a id="ex_readjdbc"></a>从PostgreSQL表中读取

执行以下步骤创建一个PXF外部表，该表引用您在上一节创建的 `forpxf_table1` PostgreSQL表， 并读取该表中的数据：

1. 指定 `Jdbc` 配置文件创建PXF外部表。例如：

    ``` sql
    gpadmin=# CREATE EXTERNAL TABLE pxf_tblfrompg(id int)
                LOCATION ('pxf://public.forpxf_table1?PROFILE=Jdbc&SERVER=pgsvrcfg')
                FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
    ```

2. 显示 `pxf_tblfrompg` 表的所有行：

    ``` sql
    gpadmin=# SELECT * FROM pxf_tblfrompg;
     id
    ----
      1
      2
      3
    (3 rows)
    ```

#### <a id="ex_writejdbc"></a>写入PostgreSQL表

执行以下步骤，将一些数据写入Postgres `forpxf_table1` 表，然后从该表中读取它们。 您必须为写操作创建一个新的外部表。

1. 指定 `Jdbc` 配置文件创建一个可写的PXF外部表。 例如：

    ``` sql
    gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_writeto_postgres(id int)
                LOCATION ('pxf://public.forpxf_table1?PROFILE=Jdbc&SERVER=pgsrvcfg')
              FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
    ```

4. 将一些数据写入 `pxf_writeto_postgres` 表。 例如：

    ``` sql
    =# INSERT INTO pxf_writeto_postgres VALUES (111);
    =# INSERT INTO pxf_writeto_postgres VALUES (222);
    =# INSERT INTO pxf_writeto_postgres VALUES (333);
    ```

5. 使用您在上一节创建的可读外部表 `pxf_tblfrompg` 来查看PostgreSQL `forpxf_table1` 表中的数据：

    ``` sql
    gpadmin=# SELECT * FROM pxf_tblfrompg ORDER BY id DESC;
     id
    -----
     333
     222
     111
       3
       2
       1
    (6 rows)
    ```

